﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_DBGM
    {
        private static SqlConnection sqlcon;

        public static SqlConnection Sqlcon
        {
            get
            {
                sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ZFDBConnectionByIIS"].ConnectionString);     
                //sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ZFDBConnectionBySP100"].ConnectionString); 
                
                switch (sqlcon.State)
                {
                    case ConnectionState.Broken:
                        sqlcon.Close();
                        sqlcon.Open();
                        break;
                    case ConnectionState.Closed:
                        sqlcon.Open();
                        break;
                    case ConnectionState.Connecting:
                        break;
                    case ConnectionState.Executing:
                        break;
                    case ConnectionState.Fetching:
                        break;
                    case ConnectionState.Open:
                        break;
                    default:
                        break;
                }
                return DAL_DBGM.sqlcon;
            }
            set { DAL_DBGM.sqlcon = value; }
        }

        private static void CloseDBConnection()
        {
            try
            {
                sqlcon.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public static DataSet GetDataSet(string strSQL, params SqlParameter[] values)
        {
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = strSQL;
                com.CommandTimeout = 60;
                com.Parameters.AddRange(values);
                com.Connection = Sqlcon;
                SqlDataAdapter Adapter = new SqlDataAdapter(com);
                DataSet set = new DataSet();
                Adapter.Fill(set);
                return set;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDBConnection();
            }           
        }

        public static bool GetInfoByReader(string strSQL, params SqlParameter[] values)
        {
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = strSQL;
                com.CommandTimeout = 60;
                com.Parameters.AddRange(values);
                //          com.CommandType = CommandType.StoredProcedure;
                com.Connection = Sqlcon;
                SqlDataReader reader = com.ExecuteReader();
                if (reader.Read())
                {
                    reader.Close();
                    Sqlcon.Close();
                    return true;
                }
                else
                {
                    reader.Close();
                    Sqlcon.Close();
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDBConnection();
            }
        }

        public static bool InsertInfo(string strSQL, params SqlParameter[] values)
        {
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = strSQL;
                com.CommandTimeout = 60;
                //           com.CommandType = CommandType.StoredProcedure;
                com.Connection = Sqlcon;
                com.Parameters.AddRange(values);
                int i = com.ExecuteNonQuery();
                if (i > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDBConnection();
            }
        }

        public static bool DeleteInfo(string strSQL, params SqlParameter[] values)
        {
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = strSQL;
                com.CommandTimeout = 60;
                com.Connection = Sqlcon;
                com.Parameters.AddRange(values);
                int i = com.ExecuteNonQuery();
                if (i > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDBConnection();
            }
        }

        public static bool UpdateInfo(string strSQL, params SqlParameter[] values)
        {
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = strSQL;
                com.CommandTimeout = 60;
                com.Connection = Sqlcon;
                com.Parameters.AddRange(values);
                int i = com.ExecuteNonQuery();
                if (i > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (System.Exception)
            {
                return false;
            }
            finally
            {
                CloseDBConnection();
            }
        }
    }
}
